package com.dao;
import org.apache.naming.java.javaURLContextFactory;
//import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;
import com.moddel.*;
import java.sql.*;
import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import java.io.*;
import javax.naming.Context;   
import javax.sql.DataSource;  
import javax.naming.InitialContext;
import javax.naming.NamingException;   
//查询数据
public class NewsDao {
	private final static String DRIVER = "com.mysql.jdbc.Driver";
	private final static String URL = "jdbc:mysql://localhost:3306/mafx";
	private final static String USERNAME = "root";
	private final static String PASSWORD = "root";
	private int pagesize=0;
	private int pagenow=1;
	public ArrayList<news> selectAllNews(int pageSize,int pageNow) throws SQLException, NamingException{
		//查询操作
		Connection ct=null;
		PreparedStatement ps = null;
		ResultSet rSet=null;
		this.pagesize=pageSize;
		this.pagenow=pageNow;
		ArrayList<news> list=new ArrayList<news>();
		String sql="SELECT id,title,type,promulgator,date  FROM news LIMIT ?,?";
	
		try{
	
			 //传入pagesize和pagenow开始查询
			 Class.forName(DRIVER);
			 ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);			 
			 ps=ct.prepareStatement(sql) ;
			 ps.setInt(1, (pagenow-1)*pagesize);
			 ps.setInt(2, pagesize);
			 rSet=ps.executeQuery();
		     while(rSet.next()){
				 news newso=new news();
				 newso.setId(rSet.getInt(1));
				 newso.setTitle(rSet.getString(2));
				 newso.setType(rSet.getString(3));
				 newso.setMaker(rSet.getString(4));
				 newso.setDate(rSet.getString(5));
				 list.add(newso);
		    }
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			
			if(rSet!=null)
				rSet.close();
			if(ps!=null)
				ps.close();
			if(ct!=null)
				ct.close();
		}
		return list;
		
     }
	public ArrayList<news> selectNewsDetail(int id){
		ArrayList<news> list=new ArrayList<news>();
		Connection ct=null;
		PreparedStatement ps1=null;
		ResultSet rSet1=null;
		PreparedStatement ps2=null;
		ResultSet rSet2=null;
		String sql="select title,type,promulgator,date,content FROM news where id="+id+"";
		try{
			Class.forName(DRIVER);
			ct=DriverManager .getConnection(URL,USERNAME,PASSWORD);
			ps1=ct.prepareStatement(sql);
			rSet1=ps1.executeQuery();
			while(rSet1.next()){
				news ns=new news();
				ns.setTitle(rSet1.getString(1));
				ns.setType(rSet1.getString(2));
				ns.setMaker(rSet1.getString(3));
				ns.setDate(rSet1.getString(4));
				ns.setContent(rSet1.getString(5));
			    ps2=ct.prepareStatement("select newsimage.image1,newsimage.image2,newsimage.image3,newsimage.image4 from news,newsimage where news.id="+id+" and (newsimage.id=news.tid)");
			    rSet2=ps2.executeQuery();
			    rSet2.next();
			    ns.setImage1(rSet2.getString(1));
			    ns.setImage2(rSet2.getString(2));
			    ns.setImage3(rSet2.getString(3));
			    ns.setImage4(rSet2.getString(4));
			    list.add(ns);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
		
			try {
				if(rSet1!=null)
					rSet1.close();
				if(rSet2!=null)
					rSet2.close();
				if(ps1!=null)
					ps1.close();
				if(ps2!=null)
				    ps2.close();
				if(ct!=null)
				    ct.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		}
	    return list;
	}
	
	//得到数据库的总信息数
	public int getRowcount() throws SQLException{
		Connection ct=null;
		PreparedStatement ps = null;
		ResultSet rSet=null;
		int rowCount = 0;
		String sql="SELECT * FROM news";
		try{
		Class.forName(DRIVER);
		ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		ps=ct.prepareStatement(sql);//获取总信息
		rSet=ps.executeQuery();
		rSet.last();//指针知道最后一条信息
		rowCount=rSet.getRow();//得到最后一条的行号即为总信息数
		}catch(Exception ex){
		ex.printStackTrace();	
		}finally{
			if(rSet!=null)
				rSet.close();
			if(ps!=null)
				ps.close();
			if(ct!=null)
				ct.close();
		}
	return rowCount;
	}
	/**********************添加新闻***************/
	public boolean insertNews(int tid,String title, String type, String maker, String date, String content){
		Connection ct=null;
		PreparedStatement ps=null;
		PreparedStatement ps2=null;
		int rSet=0;
		int rSet2=0;
		boolean ifAdd=false;
		String sql="insert into news(title,type,promulgator,content,date,tid) values('"+title+"','"+type+"','"+maker+"','"+content+"','"+date+"',"+tid+")";
		try{
			Class.forName(DRIVER);
			ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
			ps=ct.prepareStatement(sql);
			rSet=ps.executeUpdate();
			if(rSet==1)
			  ifAdd=true;
		}catch(Exception ex){
		     ex.printStackTrace();	
		}finally{
			
				try {
					if(ps!=null)
					    ps.close();
					if(ct!=null)
						ct.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		
		return ifAdd;	
	} 
	public boolean insertImage(int tid,String image1,String image2,String image3,String image4){
		Connection ct=null;
		PreparedStatement ps1=null;
		PreparedStatement ps2=null;
		int rSet=0;
		boolean ifAdd=false;
		String sql="insert into newsImage values("+tid+",'"+image1+"','"+image2+"','"+image3+"','"+image4+"')";
		try{
			Class.forName(DRIVER);
			ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
			ps1=ct.prepareStatement("set FOREIGN_KEY_CHECKS =0");
			ps2=ct.prepareStatement(sql);
			ps1.executeUpdate();
			rSet=ps2.executeUpdate();
			if(rSet==1)
			  ifAdd=true;
		}catch(Exception ex){
		     ex.printStackTrace();	
		}finally{
			
			try {
				if(ps1!=null)
				    ps1.close();
				if(ps2!=null)
					ps2.close();
				if(ct!=null)
					ct.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ifAdd;	
	}
	
	//修改数据
	//@SuppressWarnings("unused")
	public boolean UpdateNews(int id,String title,String type,String date,String maker) throws SQLException{
		Connection ct=null;
		PreparedStatement ps=null;
		boolean ifUpdate=false;
		int resultInt=0;
		String sql="update news set title='"+title+"',type='"+type+"',date='"+date+"',promulgator='"+maker+"' where id="+id+"";
		try{
		Class.forName(DRIVER);
		ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		ps=ct.prepareStatement(sql);
		resultInt=ps.executeUpdate();
		if(resultInt==1)
			ifUpdate=true;	
		
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)
				ps.close();
			if(ct!=null)
				ct.close();
		}
		return ifUpdate;	
	}
	//删除数据
	public boolean deleteNews(int id){
		Connection ct=null;
		PreparedStatement ps = null;
		ResultSet rSet=null;
		boolean pd=false;
		try{
		Class.forName(DRIVER);
		ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		ps=ct.prepareStatement("delete from news where id="+id);
		//ps.setInt(1, id);
		int a=ps.executeUpdate();
		if(a==1){
			pd=true;
		}
		}catch(Exception ex){
		ex.printStackTrace();	
		}finally{
			try {
				if(rSet!=null)
					rSet.close();
				if(ps!=null)
					ps.close();
				if(ct!=null)
					ct.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		
	    }
		return pd;
    }
	//登录验证
	public int login_1(String passwd){
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		int id=0;
		String sql="select userId from manger where passwd='"+passwd+"'";
		try{
		Class.forName(DRIVER);
		ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		ps=ct.prepareStatement(sql);
		rSet=ps.executeQuery();
		if(rSet!=null){
			rSet.next();
			id=rSet.getInt(1);
		}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				if(rSet!=null)
					rSet.close();
				if(ps!=null)
					ps.close();
				if(ct!=null)
					ct.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
		}
		return id;
	 }
	public int login_2(String username){
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		int id=0;
		String sql="select userId from manger where username='"+username+"'";
		try{
		Class.forName(DRIVER);
		ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		ps=ct.prepareStatement(sql);
		rSet=ps.executeQuery();
		if(rSet!=null){
			rSet.next();
			id=rSet.getInt(1);
		}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				if(rSet!=null)
					rSet.close();
				if(ps!=null)
					ps.close();
				if(ct!=null)
					ct.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
		}
	 return id;	
	 }
	//查找头像
	public String getImage(String username,String passwd){
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		String image="image/mr.jpg";
		String sql1="select username from userinfor where passwd="+passwd;
		String sql2="select userId from userinfor where username='"+username+"'";
		try{//System.out.println("mafx");
			Class.forName(DRIVER);
			ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
			ps=ct.prepareStatement(sql1);
			rSet=ps.executeQuery();
			rSet.next();
			
		if(username.equals(rSet.getString(1)))
			{
			ps=ct.prepareStatement(sql2);	
			rSet=ps.executeQuery();
			rSet.next();
			int userId=rSet.getInt(1);
			//ps=ct.prepareStatement("select img from userinfor,image where userinfor.userId="+userId+"and (image.id="+userTid+")");
			String sql3="select image.img from userinfor join image on(image.id=userinfor.tid)where (userinfor.userId="+userId+")";
			ps=ct.prepareStatement(sql3);
			rSet=ps.executeQuery();
			rSet.next();
			image=rSet.getString(1);
			}	
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				if(rSet!=null)
					rSet.close();
				if(ps!=null)
					ps.close();
				if(ct!=null)
					ct.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
		}
	return image;
		
	}
	public int insertIntoUser(userBm ubm){
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		String name=ubm.getName();
		String nianji=ubm.getNianji();
	    String xueyuan=ubm.getXueyuan();
	    String major=ubm.getMajor();
		String max=ubm.getMax();
		String want=ubm.getWant();
		String photoNumber=ubm.getPhotoNumber();
		String email=ubm.getEmail();
		String pjia=ubm.getPjia();
		String photo=ubm.getPhoto();
		String aboutmy=ubm.getAboutmy();
		System.out.println("fddss=="+pjia);
		int rsetInt=0;
		int rowCount=0;
		String sql="insert into userBm values(?,'"+name+"',"
												+ "'"+nianji+"',"
												+ "'"+xueyuan+"',"
												+ "'"+major+"',"
												+ "'"+max+"',"
												+ "'"+want+"',"
												+ "'"+photoNumber+"',"
												+ "'"+email+"',"
												+ "'"+pjia+"',"
											    + "'"+photo+"',"
												+ "'"+aboutmy+"')";
		try{
			Class.forName(DRIVER);
			ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
			ps=ct.prepareStatement("SELECT * FROM userBm");//获取总信息
			rSet=ps.executeQuery();
			rSet.last();
			rowCount=rSet.getRow();
			ps=ct.prepareStatement(sql);
			ps.setInt(1, rowCount+1);
			rsetInt=ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				if(rSet!=null)
					rSet.close();
				if(ps!=null)
					ps.close();
				if(ct!=null)
					ct.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
		}
		
		return rsetInt;
	}
	public ArrayList<userBm> getUserInfor(int id){
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		String sql="select * from userBm where id="+id+"";
		ArrayList<userBm> uBms=new ArrayList<userBm>();
		try{
			Class.forName(DRIVER);
			ct=DriverManager.getConnection(URL,USERNAME,PASSWORD);
			ps=ct.prepareStatement(sql);
			rSet=ps.executeQuery();
			while(rSet.next()){
				userBm uBm=new userBm();
				uBm.setName(rSet.getString(2));
				uBm.setNianji(rSet.getString(3));
				uBm.setXueyuan(rSet.getString(4));
				uBm.setMajor(rSet.getString(5));
				uBm.setMax(rSet.getString(6));
				uBm.setWant(rSet.getString(7));
				uBm.setPhotoNumber(rSet.getString(8));
				uBm.setEmail(rSet.getString(9));
				uBm.setPjia(rSet.getString(10));
				uBm.setPhoto(rSet.getString(11));
				uBm.setAboutmy(rSet.getString(12));
				uBms.add(uBm);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				if(rSet!=null)
					rSet.close();
				if(ps!=null)
					ps.close();
				if(ct!=null)
					ct.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			
		}
		return uBms;
	}
/**
 * @throws SQLException *****************测试程序*************************************************************/
/*public static void main(String[] args) {
	SelectUserInfor hh=new SelectUserInfor();
	/*boolean xx=hh.UpdateInfor(4,"lalx","2019","may@161",5);
	System.out.println(xx);
	//System.out.println("hh".equals("hh"));
	
}
System.out.println(hh.getImage("xzq", "123"));	
}*/
}
